<?php
namespace app\manage\controller;
use think\Db;

class InfoRole extends Base
{
    //列表
    public function index(){
        if( request()->isAjax() || input('export') ){
            $page = input('page');
            $limit = input('limit');
            $date = explodeTime(input('date'));
            $myUserId = input('my_user_id');
            $channelId = input('channel_id');
            $gameId = input('game_id');
            $serverId = input('server_id');
            $roleId = input('role_id');
            $sort = input('sort');

            $where = "a.create_time between {$date['start_time']} AND {$date['end_time']}";
            !empty($gameId) && $where .= " && a.game_id='{$gameId}'";
            !empty($serverId) && $where .= " && (a.server_id='{$serverId}' OR a.server_name like '%{$serverId}%')";
            !empty($roleId) && $where .= " && (a.role_id='{$roleId}' OR a.role_name like '%{$roleId}%')";
            !empty($myUserId) && $where .= " && (a.my_user_id='{$myUserId}' OR a.my_user_name like '%{$myUserId}%')";

            if($sort=='total_price'){
                $sort = 'b.total_price desc';
            }else if($sort=='create_time'){
                $sort = 'a.create_time desc';
            }

            if( !empty($channelId) ){
                if($channelId >= 5000){ //联运
                    $where .= " && a.channel_id='{$channelId}'";
                }else{ //CPS
                    $allChannelId = model('Channel')->allChannelId($channelId);
                    $where .= " && a.channel_id=1000 && a.cps_id in ({$allChannelId})";
                }
            }

            if(input('?export')){
                $start = 0;
                $length = 0;
            }else{
                $start = ($page-1)*$limit;
                $length = $limit;
            }

            $y1 = date('Y', $date['start_time']);
            $y2 = date('Y', $date['end_time']);

            if(1){ //同一年
                $count = Db::name('GameUserRoleY_'.$y1)->alias('a')->where($where)->count();

                $subSql = Db::table('sdk_pay_order_y_'.$y1)->where("pay_time between {$date['start_time']} AND {$date['end_time']} && debug=0")
                    ->field('my_user_id,channel_id,cps_id,server_id,role_id,sum(total_price)/100 as total_price')->group('my_user_id,channel_id,cps_id,server_id,role_id')->buildSql();

                $select = Db::name('GameUserRoleY_'.$y1)
                    ->field('a.my_user_id, a.channel_id, a.cps_id, a.game_id, a.ip, a.server_id, a.server_name, a.role_id, a.role_name, a.level, a.vip, a.create_time, b.total_price')
                    ->alias('a')
                    ->join("{$subSql} b", 'a.my_user_id=b.my_user_id && a.channel_id=b.channel_id && a.cps_id=b.cps_id && a.server_id=b.server_id && a.role_id=b.role_id', 'LEFT')
                    ->where($where)
                    ->limit($start, $length)
                    ->order($sort)
                    ->select();
            }else{ //跨年
                $field = "my_user_id, channel_id, cps_id, game_id, ip, server_id, server_name, role_id, role_name, level, vip, create_time";
                $subQuery = Db::name('GameUserRoleY_'.$y1)
                    ->field($field)
                    ->where($where)
                    ->union("select {$field} from sdk_pay_order_y_{$y2} where ({$where})", true)
                    ->buildSql();

                $count = Db::table($subQuery.' y')->count();

                $select = Db::table($subQuery.' y')
                    ->limit($start, $length)
                    ->order('create_time desc')
                    ->select();
            }

            $gameIdName = model('Game')->gameIdName();
            $channelIdName = model('Channel')->channelIdName();

            $data = [];
            foreach ($select as $k=>$v){
                if($v['channel_id']==1000){
                    $channelId = $v['cps_id'];
                    $channelName = isset($channelIdName[ $v['cps_id'] ]) ? $channelIdName[ $v['cps_id'] ] : '';
                }else{
                    $channelId = $v['channel_id'];
                    $channelName = isset($channelIdName[ $v['channel_id'] ]) ? $channelIdName[ $v['channel_id'] ] : '';

                }
                $gameName = isset($gameIdName[ $v['game_id'] ]) ? $gameIdName[ $v['game_id'] ] : '';
                $data[] = [
                    'create_time' => date('Y-m-d H:i:s', $v['create_time']),
                    'my_user_id' => $v['my_user_id'],
                    'channel_id' => $channelId,
                    'channel_name' =>  $channelName,
                    'game_id' => $v['game_id'],
                    'game_name' => $gameName,
                    'server_id' => $v['server_id'],
                    'server_name' => $v['server_name'],
                    'role_id' => $v['role_id'],
                    'role_name' => $v['role_name'],
                    'level' => $v['level'],
                    'vip' => $v['vip'],
                    'total_price' => $v['total_price'] ? $v['total_price'] : 0,
                ];
            }

            if(input('?export')){
                array_unshift($data, ['创角时间', '米娱登录ID', '渠道ID', '渠道名称', '游戏ID', '游戏名称', '区服ID', '区服名称', '角色ID', '角色名称', '等级', 'VIP', '充值金额']);
                exportExcel($data, input('date').'创角信息', input('date').'创角信息');
            }

            $this->layTable(0, '创角信息', $data, $count);
        }else{
            return view('', ['date'=>date('Y-m-d', time()).' - '.date('Y-m-d', time())]);
        }
    }

    public function pay(){
        if(request()->isAjax()) {
            $page = input('page');
            $limit = input('limit');
            $date = explodeTime(input('date'));
            $myUserId = input('my_user_id');
            $channelId = input('channel_id');
            $gameId = input('game_id');
            $serverId = input('server_id');
            $roleId = input('role_id');
            $sort = input('sort');

            $where = "pay_time between '{$date['start_time']}' AND '{$date['end_time']}' && my_user_id='{$myUserId}' && game_id='{$gameId}' && server_id='{$serverId}' && role_id='{$roleId}'";

            if ($channelId >= 5000) { //联运
                $where .= " && channel_id='{$channelId}'";
            } else { //CPS
                $allChannelId = model('Channel')->allChannelId($channelId);
                $where .= " && channel_id=1000 && reg_cps_id in ({$allChannelId})";
            }

            $y1 = date('Y', $date['start_time']);
            $y2 = date('Y', $date['end_time']);

            $start = ($page-1)*$limit;
            $length = $limit;

            if($y1 == $y2){ //同一年
                $count = Db::name('PayOrderY_'.$y1)->where($where)->count();

                $select = Db::name('PayOrderY_'.$y1)
                    ->where($where)
                    ->limit($start, $length)
                    ->order('pay_time desc')
                    ->select();

                $sum = Db::name('PayOrderY_'.$y1)
                    ->where($where)
                    ->sum('total_price');
            }else{ //跨年
                $field = "server_id, server_name, my_order_id, game_id, channel_id, reg_cps_id, total_price, my_user_id, my_user_name, cp_order_id, ch_order_id, product_name, pay_type, pay_order_id, pay_time";
                $subQuery = Db::name('PayOrderY_'.$y1)
                    ->field($field)
                    ->where($where)
                    ->union("select {$field} from sdk_pay_order_y_{$y2} where ({$where})", true)
                    ->buildSql();

                $count = Db::table($subQuery.' y')->count();

                $select = Db::table($subQuery.' y')
                    ->limit($start, $length)
                    ->order('pay_time desc')
                    ->select();

                $sum = Db::table($subQuery.' y')
                    ->sum('total_price');
            }

            $gameIdName = model('Game')->gameIdName();
            $channelIdName = model('Channel')->channelIdName();

            $data = [];
            foreach ($select as $k=>$v){
                $data[] = [
                    'pay_time' => date('Y-m-d H:i:s', $v['pay_time']),
                    'game_id' => $v['game_id'] . '：' . $gameIdName[ $v['game_id'] ],
                    'server_id' => $v['server_id'] . '：' . $v['server_name'],
                    'role_id' => $v['role_id'] . '：' . $v['role_name'],
                    'channel_id' => $v['channel_id']==1000 ? $v['reg_cps_id'] . '：' .$channelIdName[ $v['reg_cps_id'] ] : $v['channel_id'] . '：' . $channelIdName[ $v['channel_id'] ],
                    'vip' => $v['vip'],
                    'level' => $v['level'],
                    'product_name' => $v['product_name'],
                    'total_price' => $v['total_price']/100,
                ];
            }

            $this->layTable(0, '支付订单', $data, $count, round($sum/100, 2));
        }else{
            return view();
        }
    }
}